package helper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collection;

import data.ArticleID;
import data.Fact;
import data.ShopID;
import data.TagID;


public class Loader {

	private static Loader _instance = null;
	
	private Loader(){}
	
	/**
	 * Liefert Instant des Managers
	 * 
	 * @return DB2ConnectionManager
	 */
	public static Loader getInstance() {
		if (_instance == null) {
			_instance = new Loader();
		}
		return _instance;
	}
	
	
	/**
	 * Speichert Artikel
	 * @param articles
	 */
	public void loadArticles(Collection<ArticleID> articles) {

		System.out.print("Load Articles gestartet...");
		
		Connection con = DB2ConnectionManager.getInstance().getConnection();
		String insertSQL = "INSERT INTO vsisp43.articleid (articleid, name, preis, productgroup, productfamily, productcategory) VALUES (?, ?, ?, ?, ?, ?)";
		
		try {
			con.setAutoCommit(false);
			
			for (ArticleID a : articles) {

				PreparedStatement pstmt = con.prepareStatement(insertSQL);

				pstmt.setInt(1, a.getArticleid());
				pstmt.setString(2, a.getName());
				pstmt.setDouble(3, a.getPreis());
				pstmt.setString(4, a.getProductgroup());
				pstmt.setString(5, a.getProductfamily());
				pstmt.setString(6, a.getProductcategory());
				
				pstmt.executeUpdate();
				pstmt.close();
			}
			
			con.commit();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println("beendet!");
	}
	
	/**
	 * Speichert Shops
	 * @param articles
	 */
	public void loadShops(Collection<ShopID> shops) {

		System.out.print("Load Shops gestartet...");
		
		Connection con = DB2ConnectionManager.getInstance().getConnection();
		String insertSQL = "INSERT INTO vsisp43.shopid (shopid, name, stadt, region, land) VALUES (?, ?, ?, ?, ?)";
		
		try {
			con.setAutoCommit(false);
			
			for (ShopID a : shops) {

				PreparedStatement pstmt = con.prepareStatement(insertSQL);

				pstmt.setInt(1, a.getShopid());
				pstmt.setString(2, a.getName());
				pstmt.setString(3, a.getStadt());
				pstmt.setString(4, a.getRegion());
				pstmt.setString(5, a.getLand());
				
				
				pstmt.executeUpdate();
				pstmt.close();
			}
			
			con.commit();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println("beendet!");
	}
	
	/**
	 * Speichert Tage
	 * @param articles
	 */
	public void loadTage(Collection<TagID> tage) {

		System.out.print("Load Tage gestartet...");
		
		Connection con = DB2ConnectionManager.getInstance().getConnection();
		String insertSQL = "INSERT INTO vsisp43.tagid (tagid, datum, monat, quartal, jahr) VALUES (?, ?, ?, ?, ?)";
		
		try {
			con.setAutoCommit(false);
			
			for (TagID a : tage) {

				PreparedStatement pstmt = con.prepareStatement(insertSQL);

				pstmt.setInt(1, a.getTagid());
				pstmt.setDate(2, new java.sql.Date(a.getDatum().getTime()));
				pstmt.setString(3, a.getMonat());
				pstmt.setString(4, a.getQuartal());
				pstmt.setInt(5, a.getJahr());

				pstmt.executeUpdate();
				pstmt.close();
			}
			
			con.commit();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		System.out.println("beendet!");

	}
	
	/**
	 * Speichert Fakten
	 * @param articles
	 */
	public void loadFacts(Collection<Fact> facts) {

		System.out.print("Load Facts gestartet...");
		
		Connection con = DB2ConnectionManager.getInstance().getConnection();
		String insertSQL = "INSERT INTO vsisp43.facts (articleid, shopid, tagid, verkauft, umsatz) VALUES (?, ?, ?, ?, ?)";
		
		try {
			con.setAutoCommit(false);
			
			for (Fact a : facts) {

				PreparedStatement pstmt = con.prepareStatement(insertSQL);

				pstmt.setInt(1, a.getArticle().getArticleid());
				pstmt.setInt(2, a.getShop().getShopid());
				pstmt.setInt(3, a.getTag().getTagid());
				pstmt.setInt(4, a.getVerkauft());
				pstmt.setDouble(5, a.getUmsatz());

//				System.out.println("Load Fact: "+ a.getArticle().getArticleid() + " -- " + a.getShop().getShopid() + " -- " +a.getTag().getTagid() + " -- " + a.getVerkauft()+ " -- " + a.getUmsatz());
				
				pstmt.executeUpdate();
				pstmt.close();
			}
			
			con.commit();
			con.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		System.out.println("beendet!");
	}
}
